package sy.service.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import sy.pageModel.BaseEntity;
import sy.pageModel.DataGrid;
import sy.pageModel.Member;
import sy.pageModel.MemberAccount;
import sy.service.MemberServiceI;
import sy.util.DateUtil;
import sy.util.DesUtils;
import sy.util.EMailUtil;
import sy.util.StringUtil;

@Service("memberService")
public class MemberServiceImpl extends BaseService implements MemberServiceI {
	
	@Override
	public DataGrid listAllActiveMain(Member member) throws Exception {
		DataGrid dg = new DataGrid();
		String sql = "select count(*) from u_user where typeid='" + Member.MEMBER_CATEGORY_PAY + "' and statu='" + Member.MEMBER_STATUS_ID_NORMAL + "' and (p_card_id is null or p_card_id='')";
		sql = addWhere(member, sql);
		long total = template.queryForLong(sql);
		System.out.println(total);
		dg.setTotal(total);
		sql = "select p_card_id pid,a.userid user_id,a.card_id card_id,a.names name,a.statu status,a.phone mobile,a.email email,a.sqkz member_type,a.ddtt create_time," +
		          "b.xb gender,b.sfzh c_id,b.csny born_date,b.nation nation,b.gj native_place,b.zy profession,b.gddh phone,b.zffs pay_type,b.xf_money balance,b.num_money reg_charge, b.reg_money member_fee, b.hzhm passport_id,b.sfzzjh hk_mak_tw_id,b.weixin weixin,b.qq qq,b.lxdz address,b.jifen" +
				  " from u_user a, z_viphy_zk b where a.typeid='" + Member.MEMBER_CATEGORY_PAY + "' and statu='" + Member.MEMBER_STATUS_ID_NORMAL + "' and a.card_id=b.vnumber and (p_card_id is null or p_card_id='')";
		sql = addWhere(member, sql);
		sql = addOrderBy(member, sql);
		sql = addLimit(member, sql);
		System.out.println(sql);
		final List<Member> memberList = new ArrayList<Member>();
        template.query(sql, new RowCallbackHandler(){
            public void processRow(ResultSet rs) throws SQLException {
                Member member = new Member();
                member.setId(StringUtil.nullToEmpty(rs.getString("user_id")));
                member.setCardId(StringUtil.nullToEmpty(rs.getString("card_id")));
                member.setName(StringUtil.nullToEmpty(rs.getString("name")));
                member.setStatus(StringUtil.nullToEmpty(rs.getString("status")));
                member.setMobile(StringUtil.nullToEmpty(rs.getString("mobile")));
                member.setEmail(StringUtil.nullToEmpty(rs.getString("email")));
                member.setMemberType(StringUtil.nullToEmpty(rs.getString("member_type")));
                member.setCreateTime(StringUtil.nullToEmpty(rs.getString("create_time")));
                member.setGender(StringUtil.nullToEmpty(rs.getString("gender")));
                member.setCid(StringUtil.nullToEmpty(rs.getString("c_id")));
                member.setBornDate(StringUtil.nullToEmpty(rs.getString("born_date")));
                member.setNation(StringUtil.nullToEmpty(rs.getString("nation")));
                member.setNativePlace(StringUtil.nullToEmpty(rs.getString("native_place")));
                member.setProfession(StringUtil.nullToEmpty(rs.getString("profession")));
                member.setPhone(StringUtil.nullToEmpty(rs.getString("phone")));
                member.setBalance(rs.getDouble("balance"));
                member.setRegCharge(rs.getDouble("reg_charge"));
                member.setMemberFee(rs.getDouble("member_fee"));
                member.setPassportId(StringUtil.nullToEmpty(rs.getString("passport_id")));
                member.setHkMacTWId(StringUtil.nullToEmpty(rs.getString("hk_mak_tw_id")));
                member.setWeixin(StringUtil.nullToEmpty(rs.getString("weixin")));
                member.setQq(StringUtil.nullToEmpty(rs.getString("qq")));
                member.setAddress(StringUtil.nullToEmpty(rs.getString("address")));
                member.setPayType(StringUtil.nullToEmpty(rs.getString("pay_type")));
                member.setJifen(rs.getInt("jifen"));
                member.setPid(StringUtil.nullToEmpty(rs.getString("pid")));
                memberList.add(member);
            }});
		dg.setRows(memberList);		
		return dg;
	}

	@Override
	public DataGrid datagrid(Member member) throws Exception {
		DataGrid dg = new DataGrid();
		String sql = "select count(*) from u_user where typeid=?";
		if (member.getMemberType().equals("f")) sql+=" and sqkz !=?";
		if (member.getMemberType().equals("t")) sql+=" and sqkz =?";
		sql = addWhere(member, sql);
		long total = template.queryForLong(sql, new Object[]{Member.MEMBER_CATEGORY_PAY,Member.MEMBER_CATEGORY_EXPERIENCE});
		System.out.println(total);
		dg.setTotal(total);
		sql = "select p_card_id pid,a.userid user_id,a.card_id card_id,a.names name,a.statu status,a.phone mobile,a.email email,a.sqkz member_type,a.ddtt create_time," +
		          "b.xb gender,b.sfzh c_id,b.csny born_date,b.nation nation,b.gj native_place,b.zy profession,b.gddh phone,b.zffs pay_type,b.xf_money balance,b.num_money reg_charge, b.reg_money member_fee, b.hzhm passport_id,b.sfzzjh hk_mak_tw_id,b.weixin weixin,b.qq qq,b.lxdz address,b.jifen" +
				  " from u_user a, z_viphy_zk b where a.typeid=? and a.card_id=b.vnumber";
		if (member.getMemberType().equals("f")) sql+=" and a.sqkz !=? ";
		if (member.getMemberType().equals("t")) sql+=" and a.sqkz =? ";
		sql = addWhere(member, sql);
		sql = addOrderBy(member, sql);
		sql = addLimit(member, sql);
		System.out.println(sql);
		final List<Member> memberList = new ArrayList<Member>();
        template.query(sql, new Object[]{Member.MEMBER_CATEGORY_PAY,Member.MEMBER_CATEGORY_EXPERIENCE}, new RowCallbackHandler(){
            public void processRow(ResultSet rs) throws SQLException {
                Member member = new Member();
                member.setId(StringUtil.nullToEmpty(rs.getString("user_id")));
                member.setCardId(StringUtil.nullToEmpty(rs.getString("card_id")));
                member.setName(StringUtil.nullToEmpty(rs.getString("name")));
                member.setStatus(StringUtil.nullToEmpty(rs.getString("status")));
                member.setMobile(StringUtil.nullToEmpty(rs.getString("mobile")));
                member.setEmail(StringUtil.nullToEmpty(rs.getString("email")));
                member.setMemberType(StringUtil.nullToEmpty(rs.getString("member_type")));
                member.setCreateTime(StringUtil.nullToEmpty(rs.getString("create_time")));
                member.setGender(StringUtil.nullToEmpty(rs.getString("gender")));
                member.setCid(StringUtil.nullToEmpty(rs.getString("c_id")));
                member.setBornDate(StringUtil.nullToEmpty(rs.getString("born_date")));
                member.setNation(StringUtil.nullToEmpty(rs.getString("nation")));
                member.setNativePlace(StringUtil.nullToEmpty(rs.getString("native_place")));
                member.setProfession(StringUtil.nullToEmpty(rs.getString("profession")));
                member.setPhone(StringUtil.nullToEmpty(rs.getString("phone")));
                member.setBalance(rs.getDouble("balance"));
                member.setRegCharge(rs.getDouble("reg_charge"));
                member.setMemberFee(rs.getDouble("member_fee"));
                member.setPassportId(StringUtil.nullToEmpty(rs.getString("passport_id")));
                member.setHkMacTWId(StringUtil.nullToEmpty(rs.getString("hk_mak_tw_id")));
                member.setWeixin(StringUtil.nullToEmpty(rs.getString("weixin")));
                member.setQq(StringUtil.nullToEmpty(rs.getString("qq")));
                member.setAddress(StringUtil.nullToEmpty(rs.getString("address")));
                member.setPayType(StringUtil.nullToEmpty(rs.getString("pay_type")));
                member.setJifen(rs.getInt("jifen"));
                member.setPid(StringUtil.nullToEmpty(rs.getString("pid")));
                memberList.add(member);
            }});
		dg.setRows(memberList);		
		return dg;
	}
	
	private String addWhere(Member member, String sql) {
		if(!StringUtil.isNullOrEmpty(member.getCardId())) {
			sql += " and card_id like '%" + member.getCardId() + "%'";
		}
		if(!StringUtil.isNullOrEmpty(member.getName())) {
			sql += " and names like '%" + member.getName() + "%'";
		}
		if(!StringUtil.isNullOrEmpty(member.getMobile())) {
			sql += " and phone like '%" + member.getMobile() + "%'";
		}
		if(!StringUtil.isNullOrEmpty(member.getCreateTimeStart())) {
			sql += " and ddtt >= '" + member.getCreateTimeStart() + "'";
		}
		if(!StringUtil.isNullOrEmpty(member.getCreateTimeEnd())) {
			sql += " and ddtt <= '" + member.getCreateTimeEnd() + "'";
		}
		return sql;
	}
	
	private String addLimit(Member member, String sql) {
		int fromRow = (member.getPage() - 1) * member.getRows();
	    sql += " limit " + fromRow + "," + member.getRows();
	    return sql;
	}
	
	private String addOrderBy(Member member, String sql) {
		if (member.getSort() != null) {
			if("cardId".equals(member.getSort())) {
				member.setSort("card_id");
			} else if("name".equals(member.getSort())) {
				member.setSort("convert(name using gbk)");
			} else if("createTime".equals(member.getSort())) {
				member.setSort("create_time");
			} else if("mobile".equals(member.getSort())) {
				member.setSort("mobile");
			} else if("email".equals(member.getSort())) {
				member.setSort("email");
			} else if("statusName".equals(member.getSort())) {
				member.setSort("status");
			} else if("memberTypeName".equals(member.getSort())) {
				member.setSort("member_type");
			} else if("genderName".equals(member.getSort())) {
				member.setSort("gender");
			} else if("cid".equals(member.getSort())) {
				member.setSort("c_id");
			} else if("balance".equals(member.getSort())) {
				member.setSort("balance");
			} else if("regCharge".equals(member.getSort())) {
				member.setSort("reg_charge");
			} else if("memberFee".equals(member.getSort())) {
				member.setSort("member_fee");
			} else if("payTypeName".equals(member.getSort())) {
				member.setSort("pay_type");
			}
			sql += " order by " + member.getSort() + " " + member.getOrder();
		}
		return sql;
	}
	
	@Override
	@Transactional
	public Member edit(Member member) throws Exception {
		String sql = "update u_user set names=?, phone=?, email=?, id_card=? where userid=?";
		Object[] paras = new Object[]{member.getName(), member.getMobile(), member.getEmail(), member.getCid(), member.getId()};
		int affectRow = 0;
		try {
		    affectRow = template.update(sql, paras);
		} catch(Exception e) {
			e.printStackTrace();
			throw new RuntimeException("修改会员信息失败，请联系系统管理员！");
		}
		if(affectRow == 0) {
			throw new RuntimeException("会员记录不存在！");
		} else if(affectRow > 1) {
			throw new RuntimeException("存在多条会员记录，修改失败，请联系系统管理员！");
		}
		
		sql = "update z_viphy_zk set xb=?, sfzh=?, csny=?, nation=?, gj=?, zy=?, gddh=?, xm=?, email=?, sjh=?, hzhm=?, sfzzjh=?, weixin=?, qq=?, lxdz=? where vnumber=?";
		paras = new Object[] {
				member.getGender(),
				member.getCid(),
				member.getBornDate(),
				member.getNation(),
				member.getNativePlace(),
				member.getProfession(),
				member.getPhone(),
				member.getName(),
				member.getEmail(),
				member.getMobile(),
				member.getPassportId(),
				member.getHkMacTWId(),
				member.getWeixin(),
				member.getQq(),
				member.getAddress(),
				member.getCardId()
		};
		try {
		    affectRow = template.update(sql, paras);
		} catch(Exception e) {
			e.printStackTrace();
			throw new RuntimeException("修改会员信息失败，请联系系统管理员！");
		}
		if(affectRow == 0) {
			throw new RuntimeException("会员记录不存在！");
		} else if(affectRow > 1) {
			throw new RuntimeException("存在多条会员记录，修改失败，请联系系统管理员！");
		}
		return member;
	}
	
	@Override
	@Transactional
	public Member add(Member member) throws Exception {
		DesUtils des = new DesUtils("cq666666");//密码加密
		String cardId = this.genCardId(member.getMemberType());
		member.setCardId(cardId);
		member.setId(cardId);
		if (!member.getMemberType().equals(Member.MEMBER_TYPE_ID_EXPERIENCE)) {
			double memberFee = this.getMemberFee(member.getMemberType());
			double balance = member.getRegCharge() - memberFee;
			if (balance < 0) {
				throw new RuntimeException("注册充值金额不够支付会费！");
			}
			member.setMemberFee(memberFee);
			member.setBalance(balance);
			member.setStatus(Member.MEMBER_STATUS_ID_WAIT_AUDIT);
		}else{
			member.setStatus(Member.MEMBER_STATUS_ID_NORMAL);
			member.setBalance(member.getRegCharge());
		}
        member.setCreateTime(DateUtil.getCurrentDate());
		String sql = "insert into u_user (userid, card_id, names, pass, statu, ismanage, ddtt, typeid, phone, email, id_card, sqkz) " + 
		             "values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		String pass=des.encrypt(member.getMobile());
		Object[] paras = new Object[] {
				member.getId(),
				member.getCardId(),
				member.getName(),
				pass,
				member.getStatus(),
				"0",
				member.getCreateTime(),
				Member.MEMBER_CATEGORY_PAY,
				member.getMobile(),
				member.getEmail(),
				member.getCid(),
				member.getMemberType()
		};
		
		int affectRow = 0;
		try {
		    affectRow = template.update(sql, paras);
		} catch(Exception e) {
			e.printStackTrace();
			throw new RuntimeException("添加会员失败，请联系系统管理员！");
		}
		if(affectRow == 0) {
			throw new RuntimeException("添加会员失败，请联系系统管理员！");
		}
	
		sql = "insert into z_viphy_zk (id, xb, sfzh, csny, nation, gj, zy, gddh, xm, email, sjh, hzhm, sfzzjh, weixin, qq, lxdz, password, sqkz, vnumber, num_money, xf_money, reg_money, zffs) " +
		      "values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		paras = new Object[] {
				java.util.UUID.randomUUID().toString(),
				member.getGender(),
				member.getCid(),
				member.getBornDate(),
				member.getNation(),
				member.getNativePlace(),
				member.getProfession(),
				member.getPhone(),
				member.getName(),
				member.getEmail(),
				member.getMobile(),
				member.getPassportId(),
				member.getHkMacTWId(),
				member.getWeixin(),
				member.getQq(),
				member.getAddress(),
				member.getMobile(),
				member.getMemberType(),
				member.getCardId(),
				member.getRegCharge(),
				member.getBalance(),
				member.getMemberFee(),
				member.getPayType()
		};
		try {
		    affectRow = template.update(sql, paras);
		} catch(Exception e) {
			e.printStackTrace();
			throw new RuntimeException("添加会员失败，请联系系统管理员！");
		}
		if(affectRow == 0) {
			throw new RuntimeException("添加会员失败，请联系系统管理员！");
		} 
		return member;
	}
	
	@Override
	@Transactional
	public Member addFree(Member member) throws Exception {
		DesUtils des = new DesUtils("cq666666");//密码加密
		String pass=des.encrypt(member.getMobile());
		
		System.out.println(member.getCardId());
		String cardId = "";
		if(member.getCardId() != null && !"".equals(member.getCardId().trim())) {
			cardId = member.getCardId().trim();
		} else {
		    cardId = this.genCardId(member.getMemberType());
		}
		member.setCardId(cardId);
		member.setId(cardId);		
		member.setStatus(Member.MEMBER_STATUS_ID_NORMAL);
        member.setCreateTime(DateUtil.getCurrentDate());
        
		String sql = "insert into u_user (userid, card_id, names, pass, statu, ismanage, ddtt, typeid, phone, email, id_card, sqkz) " + 
		             "values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		
		Object[] paras = new Object[] {
				member.getId(),
				member.getCardId(),
				member.getName(),
				pass,
				member.getStatus(),
				"0",
				member.getCreateTime(),
				Member.MEMBER_CATEGORY_PAY,
				member.getMobile(),
				member.getEmail(),
				member.getCid(),
				member.getMemberType()
		};
		
		int affectRow = 0;
		try {
		    affectRow = template.update(sql, paras);
		} catch(Exception e) {
			e.printStackTrace();
			throw new RuntimeException("添加会员失败，可能是卡号已经被使用，请重新填写卡号或联系系统管理员！");
		}
		if(affectRow == 0) {
			throw new RuntimeException("添加会员失败，可能是卡号已经被使用，请重新填写卡号或联系系统管理员！");
		}
	
		sql = "insert into z_viphy_zk (id, xb, sfzh, csny, nation, gj, zy, gddh, xm, email, sjh, hzhm, sfzzjh, weixin, qq, lxdz, password, sqkz, vnumber, num_money, xf_money, reg_money, zffs) " +
		      "values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		paras = new Object[] {
				java.util.UUID.randomUUID().toString(),
				member.getGender(),
				member.getCid(),
				member.getBornDate(),
				member.getNation(),
				member.getNativePlace(),
				member.getProfession(),
				member.getPhone(),
				member.getName(),
				member.getEmail(),
				member.getMobile(),
				member.getPassportId(),
				member.getHkMacTWId(),
				member.getWeixin(),
				member.getQq(),
				member.getAddress(),
				member.getMobile(),
				member.getMemberType(),
				member.getCardId(),
				member.getRegCharge(),
				member.getBalance(),
				member.getMemberFee(),
				member.getPayType()
		};
		try {
		    affectRow = template.update(sql, paras);
		} catch(Exception e) {
			e.printStackTrace();
			throw new RuntimeException("添加会员失败，请联系系统管理员！");
		}
		if(affectRow == 0) {
			throw new RuntimeException("添加会员失败，请联系系统管理员！");
		} 
		return member;
	}
	
	@Override
	public Member getMemberByCardId(String cardId) throws Exception {
		if(StringUtil.isNullOrEmpty(cardId)) {
			throw new Exception("卡号为空！");
		}
		if(cardId.length() < 1) {
			throw new Exception("卡号错误！");
		}
		String tableName = cardId.startsWith("R") ? "z_ryhy_zk" : "z_viphy_zk";
		String sql = "select names,phone,xb,sfzh,csny,a.sqkz sqkz from u_user a," + tableName + " b where card_id=? and a.card_id=b.vnumber";
		System.out.println(sql);
		final List<Member> memberList = new ArrayList<Member>();
		try {
	        template.query(sql, new Object[]{cardId}, new RowCallbackHandler(){
	        public void processRow(ResultSet rs) throws SQLException {
	              Member member = new Member();
	              member.setName(StringUtil.nullToEmpty(rs.getString("names")));
	              member.setMobile(StringUtil.nullToEmpty(rs.getString("phone")));
	              member.setGender(StringUtil.nullToEmpty(rs.getString("xb")));
	              member.setCid(StringUtil.nullToEmpty(rs.getString("sfzh")));
	              member.setBornDate(StringUtil.nullToEmpty(rs.getString("csny")));
	              member.setMemberType(StringUtil.nullToEmpty(rs.getString("sqkz")));
	              memberList.add(member);
	        }}); 
        } catch(Exception e) {
        	e.printStackTrace();
        	throw new Exception("系统错误，请联系系统管理员！");
        }
		if(memberList.size() == 0) {
			throw new Exception("错误的卡号！");
		}
        return memberList.get(0);
	}

	@Override
	public List<BaseEntity> findPhoneList(String str) {
		String sql="select * from u_user where u_user.phone like '%"+str+"%' ";
		final List<BaseEntity> entityList=new ArrayList<>();
		template.query(sql, new Object[]{}, new RowCallbackHandler() {
			@Override
			public void processRow(ResultSet rs) throws SQLException {
				BaseEntity entity=new BaseEntity();
				entity.setId(rs.getString("phone"));
				entity.setText("["+rs.getString("phone")+"]"+rs.getString("names"));
				entityList.add(entity);
			}
		});
		return entityList;
	}

	private String genCardId(String memberType) throws Exception {
		try {
			String sql = "insert into u_card_no_seq values()";
			template.update(sql);
			sql = "select LAST_INSERT_ID()";
			int seq = template.queryForInt(sql);
			String result = (Member.MEMBER_CARD_ID_PREFIX_MAP.containsKey(memberType) ? Member.MEMBER_CARD_ID_PREFIX_MAP.get(memberType) : "") + seq;
			System.out.println("card id is " + result);
			return Member.MEMBER_CARD_ID_PREFIX_MAP.get(memberType) + seq;
		} catch(Exception e) {
			e.printStackTrace();
			throw new RuntimeException("生成卡号失败，请联系系统管理员！");
		}
	}
	
	private double getMemberFee(String memberType) throws Exception {
		try {
			String sql = "select reg_money from secondary_card WHERE typeid=?";
			Double result = template.queryForObject(sql, new Object[]{memberType}, Double.class);
			System.out.println("member fee is " + result);
			return result.doubleValue();
		} catch(Exception e) {
			e.printStackTrace();
			throw new RuntimeException("查询会费失败，请联系系统管理员！");
		}
	}
	
	@Override
	public DataGrid waitAuditDatagrid(Member member) throws Exception {
		DataGrid dg = new DataGrid();
		String sql = "select count(*) from u_user where typeid=? and statu=?";
		System.out.println(sql);
		long total = template.queryForLong(sql, new Object[]{Member.MEMBER_CATEGORY_PAY, Member.MEMBER_STATUS_ID_WAIT_AUDIT});
		System.out.println(total);
		dg.setTotal(total);
		
		sql = "select a.userid user_id,a.card_id card_id,a.names name,a.statu status,a.phone mobile,a.email email,a.sqkz member_type,a.ddtt create_time," +
		          "b.xb gender,b.sfzh c_id,b.csny born_date,b.nation nation,b.gj native_place,b.zy profession,b.gddh phone,b.zffs pay_type,b.xf_money balance,b.num_money reg_charge, b.reg_money member_fee, b.hzhm passport_id,b.sfzzjh hk_mak_tw_id,b.weixin weixin,b.qq qq,b.lxdz address" +
				  ",b.lsh t_number from u_user a, z_viphy_zk b where a.typeid=? and a.statu=? and a.card_id=b.vnumber";
		sql = addOrderBy(member, sql);
		sql = addLimit(member, sql);
		final List<Member> memberList = new ArrayList<Member>();
        template.query(sql, new Object[]{Member.MEMBER_CATEGORY_PAY, Member.MEMBER_STATUS_ID_WAIT_AUDIT}, new RowCallbackHandler(){
            public void processRow(ResultSet rs) throws SQLException {
                Member member = new Member();
                member.setId(StringUtil.nullToEmpty(rs.getString("user_id")));
                member.setCardId(StringUtil.nullToEmpty(rs.getString("card_id")));
                member.setName(StringUtil.nullToEmpty(rs.getString("name")));
                member.setStatus(StringUtil.nullToEmpty(rs.getString("status")));
                member.setMobile(StringUtil.nullToEmpty(rs.getString("mobile")));
                member.setEmail(StringUtil.nullToEmpty(rs.getString("email")));
                member.setMemberType(StringUtil.nullToEmpty(rs.getString("member_type")));
                member.setCreateTime(StringUtil.nullToEmpty(rs.getString("create_time")));
                member.setGender(StringUtil.nullToEmpty(rs.getString("gender")));
                member.setCid(StringUtil.nullToEmpty(rs.getString("c_id")));
                member.setBornDate(StringUtil.nullToEmpty(rs.getString("born_date")));
                member.setNation(StringUtil.nullToEmpty(rs.getString("nation")));
                member.setNativePlace(StringUtil.nullToEmpty(rs.getString("native_place")));
                member.setProfession(StringUtil.nullToEmpty(rs.getString("profession")));
                member.setPhone(StringUtil.nullToEmpty(rs.getString("phone")));
                member.setBalance(rs.getDouble("balance"));
                member.setRegCharge(rs.getDouble("reg_charge"));
                member.setMemberFee(rs.getDouble("member_fee"));
                member.setPassportId(StringUtil.nullToEmpty(rs.getString("passport_id")));
                member.setHkMacTWId(StringUtil.nullToEmpty(rs.getString("hk_mak_tw_id")));
                member.setWeixin(StringUtil.nullToEmpty(rs.getString("weixin")));
                member.setQq(StringUtil.nullToEmpty(rs.getString("qq")));
                member.setAddress(StringUtil.nullToEmpty(rs.getString("address")));
                member.setPayType(StringUtil.nullToEmpty(rs.getString("pay_type")));
				member.setSerialNumber(rs.getString("t_number"));
                memberList.add(member);
            }});
		dg.setRows(memberList);		
		return dg;
	}
	
	@Override
	@Transactional
	public Member audit(Member member) throws Exception {
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		double memberFee=0.00;
		final String[] endTimes = {""},startTimes={""};
		final int[] day = {0};
		String sql = "update u_user set statu=? where userid=?";
		if (member.getStatus().equals(Member.MEMBER_STATUS_ID_AUDIT_SUCCEEDED))  member.setStatus(Member.MEMBER_STATUS_ID_NORMAL);
		Object[] paras = new Object[]{member.getStatus(), member.getId()};
		int affectRow = 0;
		String beginTime="",endTime="",state="失败";
		try {
			affectRow = template.update(sql, paras);
			sql="SELECT *from activity_time where is_use=?";
			template.query(sql, new Object[]{BaseEntity.BASE_TYPE_ID_Y}, new RowCallbackHandler() {
				@Override
				public void processRow(ResultSet rs) throws SQLException {
					startTimes[0]=rs.getString("start_time");
					endTimes[0] = rs.getString("end_time");
					day[0] =rs.getInt("day");
				}
			});
			//当前时间加上天数（）获取最后的时间
			Calendar c = Calendar.getInstance();
			System.out.println("当前日期:"+format.format(c.getTime()));
			c.add(Calendar.DAY_OF_MONTH, day[0]);//添加天数后的时间
			System.out.println("增加后的日期 ： "+format.format(c.getTime()));
			beginTime=format.format(new Date());//获取当前时间
			Date bt=format.parse(beginTime);
			Date et=format.parse(endTimes[0]);
			Date st=format.parse(startTimes[0]);
			System.out.println(et);
			if (bt.before(et) && st.before(bt)) endTime=format.format(c.getTime());//添加天数后的时间 当前时间小于活动结束时间
			else {
				c = Calendar.getInstance();
				c.add(Calendar.DAY_OF_MONTH, 365);
				endTime=format.format(c.getTime());
			}
			//查询会员费
			String sqls="select reg_money FROM secondary_card WHERE typeid=?";
			memberFee=template.queryForInt(sqls,new Object[]{member.getMemberType()});
			if (Member.MEMBER_STATUS_ID_NORMAL.equals(member.getStatus())) {
				//修改会员有效期
				sql = "UPDATE z_viphy_zk SET jh_time=?,v_time=? where vnumber=?";
				paras = new Object[]{StringUtil.getDate(),endTime, member.getId()};
				template.update(sql, paras);
				//添加收支表
				sql = "insert into xj_transaction_record(typeid,t_time,money,balance,t_number,vnumber) values(?,?,?,?,?,?)";
				paras = new Object[]{Member.MEMBER_PAY_REGISTER, StringUtil.getDate(), member.getRegCharge(), member.getRegCharge(), member.getSerialNumber(), member.getId()};
				template.update(sql, paras);
				sql = "insert into xj_transaction_record(typeid,t_time,money,balance,t_number,vnumber) values(?,?,?,?,?,?)";
				paras = new Object[]{Member.MEMBER_PAY_CONSUME, StringUtil.getDate(), memberFee, (member.getRegCharge() - memberFee), "", member.getId()};
				template.update(sql, paras);
				state="通过";
			}
			if (!"2".equals(member.getStatus())) {
				String str[] = findEmail(BaseEntity.BASE_MASSAGE_THREE);
				EMailUtil.sendMail(str, "会员审核结果反馈", "<html><body>卡号为“" + member.getId() + "”的会员审核"+state+"</body></html>");
			}
		} catch(Exception e) {
			e.printStackTrace();
			throw new RuntimeException("审核会员失败，请联系系统管理员！");
		}
		if(affectRow == 0) {
			throw new RuntimeException("会员记录不存在！");
		} else if(affectRow > 1) {
			throw new RuntimeException("存在多条会员记录，审核失败，请联系系统管理员！");
		}
		return member;
	}
	
	@Override
	@Transactional
	public Member recharge(Member member) throws Exception {
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		double recharge = member.getRecharge();
		if(recharge == 0) {
			throw new Exception("充值金额不能为0！");
		}
		System.out.println("会员'" + member.getName() + "(" + member.getCardId() + ")'充值" + recharge + "元...");
		String sql = "update z_viphy_zk set num_money=num_money + ?,xf_money=xf_money + ? where vnumber=?";
		Object[] paras = new Object[]{recharge, recharge, member.getCardId()};
		int affectRow = 0;
	    affectRow = template.update(sql, paras);
		if(affectRow == 0) {
			throw new RuntimeException("会员充值失败！");
		}
		
		sql = "insert into xj_transaction_record(typeid,t_time,money,balance,vnumber) values(?,?,?,?,?)";
		paras = new Object[]{Member.MEMBER_PAY_RECHARGE, StringUtil.getDate(), recharge, member.getBalance() + recharge, member.getCardId()};
		template.update(sql, paras);
		return member;
	}
	
	@Override
	@Transactional
	public Member editPay(Member member) throws Exception {
		System.out.println(member.getMemberType());
		double memberFee = this.getMemberFee(member.getMemberType());
		double balance = member.getRegCharge() - memberFee;
		if(balance < 0) {
			throw new RuntimeException("注册充值金额不够支付会费！");
		}
		member.setMemberFee(memberFee);
        member.setBalance(balance);
        member.setStatus(Member.MEMBER_STATUS_ID_WAIT_AUDIT);
        
        String sql = "update u_user set statu=?,sqkz=? where userid=?";
		Object[] paras = new Object[]{member.getStatus(), member.getMemberType(), member.getId()};
		int affectRow = 0;
		try {
		    affectRow = template.update(sql, paras);
			String str[]=findEmail(BaseEntity.BASE_MASSAGE_TWO);
			EMailUtil.sendMail(str, "新会员注册（请审核）", "<html><body>卡号为“"+member.getId()+"”的会员注册了一个会员，请审核</body></html>");
		} catch(Exception e) {
			e.printStackTrace();
			throw new RuntimeException("修改会员支付信息失败，请联系系统管理员！");
		}
		if(affectRow == 0) {
			throw new RuntimeException("会员记录不存在！");
		} else if(affectRow > 1) {
			throw new RuntimeException("存在多条会员记录，修改会员支付信息失败，请联系系统管理员！");
		}
		
		sql = "update z_viphy_zk set sqkz=?, num_money=?, xf_money=?, reg_money=?, zffs=? where vnumber=?";
		paras = new Object[] {
				member.getMemberType(),
				member.getRegCharge(),
				member.getBalance(),
				member.getMemberFee(),
				member.getPayType(),
				member.getCardId()
		};
		try {
		    affectRow = template.update(sql, paras);
		} catch(Exception e) {
			e.printStackTrace();
			throw new RuntimeException("修改会员支付信息失败，请联系系统管理员！");
		}
		if(affectRow == 0) {
			throw new RuntimeException("会员记录不存在！");
		} else if(affectRow > 1) {
			throw new RuntimeException("存在多条会员记录，修改会员支付信息失败，请联系系统管理员！");
		}
		return member;
	}
	
	@Override
	public List<MemberAccount> getMemberAccounts() throws Exception {
		String sql = "select a.xm,b.ddtt,a.vnumber,b.sqkz,a.num_money,a.reg_money,a.xf_money from z_viphy_zk a,u_user b where a.vnumber=b.card_id and b.statu='1' and (b.p_card_id is null or b.p_card_id='') and b.sqkz!='0' order by b.ddtt";
		System.out.println(sql);
		final List<MemberAccount> memberAccountList = new ArrayList<MemberAccount>();
		final int[] index = new int[1];
        template.query(sql, new RowCallbackHandler() {
            public void processRow(ResultSet rs) throws SQLException {
            	MemberAccount item = new MemberAccount();
            	index[0]++;
            	item.setId(index[0]);
                item.setName(rs.getString("xm"));
                item.setRegDate(rs.getString("ddtt"));
                item.setCardId(rs.getString("vnumber"));
                item.setMemberType(rs.getString("sqkz"));
                item.setRechargeFee(rs.getDouble("num_money"));
                item.setMemberFee(rs.getDouble("reg_money"));
                item.setBalance(rs.getDouble("xf_money"));
                item.setConsumeFee(item.getRechargeFee() - item.getMemberFee());
                item.setTotalConsumed(item.getConsumeFee() - item.getBalance());
            	memberAccountList.add(item);
            }});
		return memberAccountList;
	}

}
